using System;
using System.Data;
using System.Data.Sql;
using System.Data.OleDb;
using System.Text;
using System.Globalization;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

/*=====================================================================

  File:      StoreServerComponents.cs for Adventure Works Cycles Storefront Sample
  Summary:   Server-side components which enable OLAP integration for product upsell
  Date:	     April 7, 2004

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= */

namespace Microsoft.Samples.SqlServer
{
	/// <summary>
	///   Server-side methods invoked by SQL Server to assist in processing web site requests
	/// </summary>
	public sealed class StoreServerComponents
	{

		private StoreServerComponents()
		{
		}

		[SqlMethod]
		public static void GetProductRecommendations(String cartID)
		{
            StringBuilder sb = new StringBuilder();
            using (SqlConnection conn = new SqlConnection("context connection = true"))
            {

                const int numberOfModels = 5;
                const int modelNameColumnNumber = 0;
                SqlCommand cartCommand = conn.CreateCommand();

                cartCommand.CommandText = "usp_ShoppingCartListTopModels";
                cartCommand.CommandType = CommandType.StoredProcedure;

                SqlParameter cartIDParameter = new SqlParameter("@CartID", SqlDbType.NVarChar, 50);

                cartIDParameter.Value = cartID;
                cartCommand.Parameters.Add(cartIDParameter);

                SqlDataReader cartReader = cartCommand.ExecuteReader();
                
                try
                {
                    int i = 0;

                    while (cartReader.Read() && i < numberOfModels)
                    {
                        if (i++ != 0) sb.Append(" UNION ");

                        sb.Append(String.Format(CultureInfo.InvariantCulture, "SELECT '{0}' AS [Model] ", (String)cartReader[modelNameColumnNumber]));
                    }

                    if (i == 0) return;
                }
                finally
                {
                    cartReader.Close();
                }
            }

            using (OleDbConnection conn = new OleDbConnection("Provider=MSOLAP;Initial catalog=Adventure Works DW;Data Source=localhost;"))
            {
                OleDbCommand predictCommand = conn.CreateCommand();

                string predictionQuery = "SELECT " + "Predict(Association.[v Assoc Seq Line Items], 5) " + "From Association " + "NATURAL PREDICTION JOIN " + "(SELECT (" + sb.ToString() + ") AS [v Assoc Seq Line Items]) AS t";

                predictCommand.CommandText = predictionQuery;
                conn.Open();
                using (OleDbDataReader predictReader = predictCommand.ExecuteReader())
                {
                    sb.Length = 0;
                    while (predictReader.Read())
                    {
                        object subReader = predictReader[0];

                        if (subReader is OleDbDataReader)
                        {
                            int j = 0;

                            while (((OleDbDataReader)subReader).Read())
                            {
                                if (j++ != 0) sb.Append(", ");

                                sb.Append("'");
                                sb.Append(((OleDbDataReader)subReader)[0].ToString());
                                sb.Append("'");
                            }

                            break;
                        }
                    }
                }
            }

            using (SqlConnection conn = new SqlConnection("context connection = true"))
            {
                SqlCommand resultCommand = conn.CreateCommand();

                resultCommand.CommandText = String.Format(CultureInfo.InvariantCulture, "WITH ModelProducts(ProductID) AS " + "(SELECT  max(P.ProductID) AS ProductID FROM Production.Product AS P " + " JOIN Production.ProductModel as PM ON P.ProductModelID = PM.ProductModelID " + " WHERE PM.Name IN ({0}) " + " GROUP BY PM.[Name]) " + " SELECT MP.ProductID, IP.[Name] FROM ModelProducts AS MP " + " JOIN Production.Product as IP ON MP.ProductID = IP.ProductID; ", sb.ToString());
                SqlContext.Pipe.Send(resultCommand.ExecuteReader());
            }
		}
	}
}
